﻿using System;
using System.Collections.Generic;
using System.Text;
using AD.Model;
using System.Data.SqlClient;
using System.Data;
namespace AD.DAL
{
    public class InviteFlowDAL : DALBase
    {
        public static IList<InviteFlowMod> QueryByPage(string filter, int pageSize, int index, out int maxcount)
        {
            maxcount = 0;
            try
            {
                maxcount = Count(filter);
                //string sql = "select top {0} * from (select top {1} * from tab_invite_flow_info order by AutoID asc ) as a order by AutoID desc";
                //sql = string.Format(sql, pageSize, index * pageSize);
                string sql = "select top {0} * from tab_invite_flow_info where (AutoID NOT IN (select top {1} AutoID from tab_invite_flow_info order by AutoID)) order by AutoID";
                sql = string.Format(sql, pageSize, index * pageSize);
                if (!string.IsNullOrEmpty(filter))
                {
                    sql = "select top {0} * from tab_invite_flow_info where {2} and (AutoID NOT IN (select top {1} AutoID from tab_invite_flow_info where {2} order by AutoID)) order by AutoID";
                    sql = string.Format(sql, pageSize, index * pageSize, filter);
                }
                DataTable dt = ExecuteDataTable(sql);
                if (dt == null || dt.Rows.Count <= 0) return null;
                IList<InviteFlowMod> accountFlows = new List<InviteFlowMod>();
                foreach (DataRow row in dt.Rows)
                {
                    InviteFlowMod user = SetModel(row);
                    accountFlows.Add(user);
                }
                return accountFlows;
            }
            catch { return null; }
        }
        public static int Count(string filter)
        {
            return QueryFilterToTable(filter).Rows.Count;
        }
        public static IList<InviteFlowMod> QueryFilterToList(string filter)
        {
            try
            {
                DataTable dt = QueryFilterToTable(filter);
                if (dt == null || dt.Rows.Count <= 0) return null;
                List<InviteFlowMod> inviteFlows = new List<InviteFlowMod>();
                foreach (DataRow row in dt.Rows)
                {
                    InviteFlowMod user = SetModel(row);
                    inviteFlows.Add(user);
                }
                return inviteFlows;
            }
            catch
            {
            }
            return null;
        }

        public static InviteFlowMod CheckIP(InviteFlowMod inviteFlow)
        {
            try
            {
                string filter = " InviteId='" + inviteFlow.InviteId + "'";
                DataTable dt = QueryFilterToTable(filter);
                if (dt == null || dt.Rows.Count <= 0) return null;
                foreach (DataRow row in dt.Rows)
                {
                    InviteFlowMod tmp = SetModel(row);
                    if (tmp != null)
                    {
                        if (GetHours(DateTime.Now, tmp.Commit_Date) <= 24)
                            return tmp;
                    }
                }
            }
            catch { }
            return null;
        }

        public static int Insert(InviteFlowMod inviteFlow)
        {
            try
            {
                if (SQL_CONNECT.State != ConnectionState.Open)
                    SQL_CONNECT.Open();
                SqlTransaction sqlTransaction = SQL_CONNECT.BeginTransaction();
                try
                {
                    if (CheckIP(inviteFlow) != null) return 0;
                    string sql = "insert into tab_invite_flow_info(UserId,InviteId,Status,RequestIp,Commit_Date,Remarks,BalancePoint) values(@UserId,@InviteId,@Status,@RequestIp,@Commit_Date,@Remarks,@BalancePoint)";
                    SqlParameter[] sqlParams = GetSqlParameter(inviteFlow);
                    SqlCommand sqlCommand = new SqlCommand();
                    PrepareCommand(sqlCommand, sqlTransaction, sql, sqlParams);
                    int effect = sqlCommand.ExecuteNonQuery();
                    sqlCommand.Parameters.Clear();

                    if (inviteFlow.Status && effect >= 1)
                    {
                        effect = AccountFlowDAL.Insert(sqlCommand, sqlTransaction, new AccountFlowMod() { BalancePoint = 1, OperationType = OperationType.Invite, UserId = inviteFlow.UserId });
                    }
                    if (effect >= 1)
                        sqlTransaction.Commit();
                    else
                        sqlTransaction.Rollback();
                    return effect;
                }
                catch
                {
                    sqlTransaction.Rollback();
                    return 0;
                }
            }
            catch
            {
                return 0;
            }
        }

        public static DataTable QueryFilterToTable(string filter)
        {
            try
            {
                string sql = "Select * from tab_invite_flow_info ";
                if (!string.IsNullOrEmpty(filter))
                    sql += " where " + filter;
                return ExecuteDataTable(sql);
            }
            catch
            {
            }
            return null;
        }

        #region 模型

        static InviteFlowMod SetModel(DataRow row)
        {
            InviteFlowMod inviteFlow = new InviteFlowMod();
            inviteFlow.AutoID = int.Parse(row["AutoID"].ToString());

            if (row["BalancePoint"] != DBNull.Value)
                inviteFlow.BalancePoint = int.Parse(row["BalancePoint"].ToString());

            if (row["Commit_Date"] != DBNull.Value)
                inviteFlow.Commit_Date = DateTime.Parse(row["Commit_Date"].ToString());

            if (row["InviteId"] != DBNull.Value)
                inviteFlow.InviteId = row["InviteId"].ToString();

            if (row["Remarks"] != DBNull.Value)
                inviteFlow.Remarks = row["Remarks"].ToString();

            if (row["RequestIp"] != DBNull.Value)
                inviteFlow.RequestIp = row["RequestIp"].ToString();

            if (row["Status"] != DBNull.Value)
                inviteFlow.Status = bool.Parse(row["Status"].ToString());

            if (row["UserId"] != DBNull.Value)
                inviteFlow.UserId = int.Parse(row["UserId"].ToString());

            return inviteFlow;
        }

        static SqlParameter[] GetSqlParameter(InviteFlowMod inviteFlow)
        {
            List<SqlParameter> param = new List<SqlParameter>();
            SqlParameter AutoID = new SqlParameter("@AutoID", SqlDbType.Int, 4, "AutoID");
            AutoID.Value = inviteFlow.AutoID;
            param.Add(AutoID);

            SqlParameter BalancePoint = new SqlParameter("@BalancePoint", SqlDbType.Int, 4, "BalancePoint");
            BalancePoint.Value = inviteFlow.BalancePoint;
            param.Add(BalancePoint);

            SqlParameter Commit_Date = new SqlParameter("@Commit_Date", SqlDbType.DateTime, 8, "Commit_Date");
            Commit_Date.Value = inviteFlow.Commit_Date;
            param.Add(Commit_Date);

            SqlParameter InviteId = new SqlParameter("@InviteId", SqlDbType.VarChar, 100, "InviteId");
            InviteId.Value = inviteFlow.InviteId;
            param.Add(InviteId);

            SqlParameter Remarks = new SqlParameter("@Remarks", SqlDbType.VarChar, 100, "Remarks");
            Remarks.Value = inviteFlow.Remarks;
            param.Add(Remarks);

            SqlParameter RequestIp = new SqlParameter("@RequestIp", SqlDbType.VarChar, 100, "RequestIp");
            RequestIp.Value = inviteFlow.RequestIp;
            param.Add(RequestIp);

            SqlParameter Status = new SqlParameter("@Status", SqlDbType.Bit, 1, "Status");
            Status.Value = inviteFlow.Status;
            param.Add(Status);

            SqlParameter UserId = new SqlParameter("@UserId", SqlDbType.Int, 4, "UserId");
            UserId.Value = inviteFlow.UserId;
            param.Add(UserId);

            return param.ToArray();
        }

        #endregion
    }
}